﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Web;
using System.Data;
using System.Globalization;

namespace Reviewer.Security
{
    internal static class MySqlConnectionHelper
    {
        internal static MySqlParameter CreateDateTimeMySqlParameter(string parameterName, DateTime dt)
        {
            MySqlParameter p = new MySqlParameter(parameterName, MySqlDbType.DateTime);
            p.Direction = ParameterDirection.Input;
            p.Value = MySqlConnectionHelper.RoundToSeconds(dt);
            return p;
        }

        internal static MySqlParameter CreateMySqlParam(string paramName, MySqlDbType MySqlDbType, object objValue)
        {
            MySqlParameter param = new MySqlParameter(paramName, MySqlDbType);

            if (objValue == null)
            {
                param.IsNullable = true;
                param.Value = DBNull.Value;
            }
            else
            {
                param.Value = objValue;
            }

            return param;
        }

        internal static string GetNullableString(MySqlDataReader reader, int col)
        {
            if (reader.IsDBNull(col) == false)
            {
                return reader.GetString(col);
            }

            return null;
        }

        static private Hashtable _Connections = Hashtable.Synchronized(new Hashtable(StringComparer.OrdinalIgnoreCase));

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]
        internal static MySqlConnectionHolder GetConnection(string connectionString)
        {
            // Lock the connections table, and see if it already exists
            lock (_Connections)
            {
                MySqlConnectionHolder holder = (MySqlConnectionHolder)_Connections[connectionString];
                if (holder != null && (holder.connectionInstance.State.Equals(System.Data.ConnectionState.Broken)))
                {
                    _Connections.Remove(connectionString);
                    holder = null;
                }

                if (holder == null)
                {
                    MySqlConnectionHolder newConnectionHolder = new MySqlConnectionHolder(new MySqlConnection(connectionString));
                    _Connections.Add(connectionString, newConnectionHolder);
                    holder = (MySqlConnectionHolder)_Connections[connectionString];
                }

                if (holder == null)
                {
                    return null;
                }

                holder.Open();
                return holder;
            }
        }

        internal static int GetPathID(MySqlConnection connection, string path)
        {
            return GetPathID(connection, path, false);
        }

        internal static int GetPathID(MySqlConnection connection, string path, bool createIfNeeded)
        {
            object lookupResult = null;

            using (MySqlCommand lookupCommand = new MySqlCommand("SELECT PathId FROM AN_Path WHERE Path = @Path", connection))
            {
                lookupCommand.Parameters.Add(new MySqlParameter("@Path", path));

                lookupResult = lookupCommand.ExecuteScalar();

                if ((lookupResult != null) && (lookupResult is int))
                {
                    return (int)lookupResult;
                }

                if (createIfNeeded)
                {
                    using (MySqlCommand createCommand = new MySqlCommand("INSERT INTO AN_Path (Path) VALUES (@Path)",
                        connection))
                    {
                        createCommand.Parameters.Add(new MySqlParameter("@Path", path));

                        if (createCommand.ExecuteNonQuery() != 0)
                        {
                            lookupResult = lookupCommand.ExecuteScalar();
                            if ((lookupResult != null) && (lookupResult is int))
                            {
                                return (int)lookupResult;
                            }
                        }
                    }
                }
            }

            return 0;
        }

        internal static int GetUserID(MySqlConnection connection, string userName)
        {
            return GetUserID(connection, userName, false, false, DateTime.Now);
        }

        internal static int GetUserID(MySqlConnection connection, string userName, bool createIfNeeded)
        {
            return GetUserID(connection, userName, createIfNeeded, false, DateTime.Now);
        }

        internal static int GetUserID(MySqlConnection connection, string userName, bool createIfNeeded, bool newUserIsAnonymous)
        {
            return GetUserID(connection, userName, createIfNeeded, newUserIsAnonymous, DateTime.Now);
        }

        internal static int GetUserID(MySqlConnection connection, string userName, bool createIfNeeded, bool newUserIsAnonymous, DateTime lastActivityDate)
        {
            if (userName == null || userName.Length < 1) // Application doesn't exist or user doesn't exist
                return 0;

            if (connection == null)
                return 0; // Wrong params!

            object lookupResult = null;

            using (MySqlCommand lookupCommand = new MySqlCommand(@"SELECT UserId FROM AN_User WHERE LOWER(UserName) = LOWER(@UserName)",
                                                            connection))
            {
                lookupCommand.Parameters.Add(new MySqlParameter("@UserName", userName));

                lookupResult = lookupCommand.ExecuteScalar();


                if (lookupResult != null)
                {
                    return int.Parse(lookupResult.ToString(), CultureInfo.InvariantCulture);
                }

                if (createIfNeeded)
                {
                    using (MySqlCommand createCommand = new MySqlCommand(@"INSERT INTO AN_User " +
                                                                    @"(UserName, IsAnonymous, LastActivityDate) " +
                                                                    @"VALUES (@UserName, @IsAnonymous, @LastActivityDate)",
                                                                    connection))
                    {
                        createCommand.Parameters.Add(new MySqlParameter("@UserName", userName));
                        createCommand.Parameters.Add(new MySqlParameter("@IsAnonymous", newUserIsAnonymous));
                        createCommand.Parameters.Add(new MySqlParameter("@LastActivityDate", new DateTime(lastActivityDate.Year, lastActivityDate.Month, lastActivityDate.Day, lastActivityDate.Hour, lastActivityDate.Minute, lastActivityDate.Second)));

                        if (createCommand.ExecuteNonQuery() != 0)
                        {
                            lookupResult = lookupCommand.ExecuteScalar();
                            if ((lookupResult != null) && (lookupResult is int))
                            {
                                return (int)lookupResult;
                            }
                        }
                    }
                }
            }

            return 0;
        }

        internal static Exception GetBetterException(Exception e, MySqlConnectionHolder holder)
        {
            try
            {
                if (!(e is MySqlException) || holder.connectionInstance == null ||
                    holder.connectionInstance.DataSource == null || holder.connectionInstance.DataSource.Length < 1)
                {
                    return e;
                }
            }
            finally
            {
                if (holder.connectionInstance != null)
                    holder.connectionInstance.Close();
            }

            return e;
        }

        internal static DateTime RoundToSeconds(DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
        }
    }
}
